<?php
/*
 * scrumbrella - Scrum board.
 *
 * Copyright (C) 2009  Eliel C. Sardanons (LU1ALY) <eliels@gmail.com>
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
 */

/*!
 * \file
 * \brief Project API.
 */

require_once("db.php");
require_once("logger.php");
require_once("sprint.php");
require_once("db_schema.php");
require_once("functions.php");

/*!
 * \brief Create a project.
 * \param name The name of the project to create.
 * \param description The project description.
 * \param beginning the date when the project start.
 * \retval < 0 on error.
 * \retval > 0 the id of the created project.
 */
function project_create($name, $description, $beginning)
{
	global $project_table;

	if (project_exist($name)) {
		logger_error("Project '$name' already exists.");
		return -1;
	}

	$res = db_query("INSERT INTO $project_table (name, description, date_beginning) 
			 VALUES ('$name', '$description', '$beginning');");
	if (db_error($res)) {
		logger_fatal("Error while creating project '$name': " . db_error_message($res));
		return -1;
	}

	$SQL = "SELECT id_project FROM $project_table WHERE name='$name'";
	$res = db_query($SQL);

	if (db_error($res)) {
		logger_fatal("Error while creating project '$name': " . db_error_message($res));
		return -1;
	}
	$data = db_fetchrow($res);

	return $data['id_project'];
}

/*!
 * \brief Check if a project exists.
 * \param name The project name.
 * \retval 0 If the project doesn't exist.
 * \retval 1 if the project exist.
 */
function project_exist($name)
{
	global $project_table;

	$res = db_query("SELECT id_project FROM $project_table WHERE name='$name'");
	if (db_error($res)) {
		logger_fatal("Error while checking if a project exists '$name': " . db_error_message($res));
		/* suppose that it doesn't exist. */
		return 0;
	}

	if (db_numrows($res) > 0) {
		$data = db_fetchrow($res);
		return $data['id_project'];
	}

	return 0;
}

/*!
 * \brief Delete a project based on the id.
 * \param id The id of the project to delete.
 * \retval < 0 on error.
 * \retval 0 on success.
 */
function project_delete($id)
{
	global $project_table;
	global $project_backlog_history_table;

	/* delete the project. */
	$res = db_query("DELETE FROM $project_table WHERE id_project = '$id'");
	if (db_error($res)) {
		logger_error("Error while deleting project $id: " . db_error_message($res));
		return -1;
	}

	/* delete the project backlog history. */
	$res = db_query("DELETE FROM $project_backlog_history_table WHERE id_project = '$id'");
	if (db_error($res)) {
		logger_error("Error while deleting project '$id' backlog history: " . db_error_message($res));
		return -1;
	}

	/* delete the sprint backlog history. */
	sprint_backlog_history_delete_byproject($id);

	return 0;
}

/*!
 * \brief Create a project backlog.
 * \param project_id The project id.
 * \param summary Summary description of the project backlog.
 * \param description of the project backlog.
 * \param id_user The user id.
 * \param priority The project backlog priority.
 * \retval 0 on success.
 * \retval < 0 on error.
 */
function project_backlog_add($project_id, $summary, $description, $id_user, $priority = 0)
{
	global $project_backlog_table;

	$SQL = "INSERT INTO $project_backlog_table (id_project, summary, description, id_user, priority, last_modified) VALUES " .
		"('$project_id', '$summary', '$description', '$id_user', '$priority', '".date("y-m-d h:i:s")."')";
	$res = db_query($SQL);
	if (db_error($res)) {
		logger_error($SQL);
		logger_error("Error while creating project backlog '$name': " . db_error_message($res));
		return -1;
	}

	return 0;
}

/*!
 * \brief Get every project assigned to a user.
 * \param user_id User id.
 * \retval NULL on error.
 * \retval The returned result set.
 */
function project_list_byuser($user_id)
{
	global $project_user_role_table;
	global $project_table;

	$projects = Array();

	$query = "SELECT * FROM $project_table AS pt, $project_user_role_table AS pur WHERE pur.id_user = '$user_id' AND pur.id_project = pt.id_project";
	$res = db_query($query);
	if (db_error($res)) {
		logger_error("Unable to get the list of projects for the user $user_id: " . db_error_message($res));
		return NULL;
	}

	if (!db_numrows($res)) {
		return NULL;
	}

	$projects_count = 0;
	while ($project = db_fetchrow($res)) {
		foreach($project as $key => $value) {
			$projects[$projects_count][$key] = $value;
		}
		$projects_count++;
	}

	return $projects;
}

/*!
 * \brief Get the total number of project backlogs.
 * \param project_id The project id.
 * \returns The total number of project backlogs.
 */
function project_backlogs_total($project_id)
{
	global $project_backlog_table;

	$query = "SELECT count(*) AS total FROM $project_backlog_table WHERE id_project = '$project_id'";
	$project_backlogs_result = db_query($query);
	if (db_error($project_backlogs_result) || !db_numrows($project_backlogs_result)) {
		logger_error("Error while calculating the project progress ($project_id): " . db_error_message($project_backlogs_result));
		return 0;
	}

	$project_backlogs = db_fetchrow($project_backlogs_result);
	return $project_backlogs["total"];
}

/*!
 * \brief Calculate the project progress.
 * \param project_id The project ID.
 * \returns The total project progress percent.
 */
function project_progress($project_id)
{
	$pbt = project_backlogs_total($project_id);
	if (!$pbt) {
		return 0;
	}
	$sbt = sprint_backlogs_total($project_id);
	if (!$sbt) {
		return 0;
	}
	return ($sbt * 100) / $pbt;
}


/*! \brief return the project name
 *  \param $project_id int ID project
 *  \retval NULL on error
 *          String project name
 */
function project_get_name($project_id)
{
	global $project_table;

	$query = "SELECT name FROM $project_table WHERE id_project = '$project_id'";
	$result = db_query($query);
	if (db_error($result) || !db_numrows($result)) {
		logger_error("Passing an invalid project id $project_id");
		return NULL;
	}

	$data = db_fetchrow($result);
	return $data["name"];
}

/*!
 * \brief Get all the project members of the requested project.
 * \param project_id The project id.
 * \param member_type (optional) If specified, only get the members of this type.
 * \retval an array with the members and their role in the project.
 * \retval NULL on error.
 */
function project_get_members($project_id, $member_type = NULL)
{
	global $project_table;
	global $project_user_role_table;
	global $user_table;
	global $role_table;

	$members = Array();

	$query = "SELECT u.*, r.name AS role FROM $project_user_role_table AS put, $user_table AS u, $role_table AS r " .
		"WHERE put.id_project = '$project_id' AND put.id_user = u.id_user AND put.id_role = r.id_role ORDER BY r.id_role";

	$result = db_query($query);
	if (db_error($result) || !db_numrows($result)) {
		logger_error("Error while getting the list of members for project $project_id");
		return NULL;
	}

	$members_count = 0;
	while ($data = db_fetchrow($result)) {
		if (!$member_type || $member_type && !strcasecmp($data["role_name"], $member_type)) {
			foreach($data as $key => $value) {
				$members[$members_count][$key] = $value;
			}
			$members_count++;
		}
	}

	return $members;
}

/*!
 * \brief Get the last sprint created for a project and its ID.
 * \param project_id The project ID.
 * \retval NULL on error.
 * \retval An array with the sprint number and the sprint id.
 */
function project_get_lastsprint($project_id)
{
	global $project_table;
	$sprint = Array();

	$query = "SELECT number, id_sprint FROM sprint WHERE id_project='$project_id' ORDER BY number DESC LIMIT 1";

	$result = db_query($query);
	if (db_error($result) || !db_numrows($result)) {
		logger_error("Unable to get a sprint for the specified project with id $project_id");
		return NULL;
	}

	$data = db_fetchrow($result);
	$sprint["number"] = $data["number"];
	$sprint["id"] = $data["id_sprint"];

	return $sprint;
}

/*!
 * \brief Check if a project name is already registered.
 * \param project_name Project name to verify.
 * \retval -1 on error.
 * \retval 0 if the project name is NOT being used.
 * \retval 1 if the project name is being used.
 */
function project_check_name($project_name,$id=0)
{
	global $project_table;

	if ($id == 0) {
		$query = "SELECT * FROM $project_table WHERE name = '$project_name' LIMIT 1";
	} else {
		$query = "SELECT * FROM $project_table WHERE name = '$project_name' AND NOT id_project = $id";
	}

	$result = db_query($query);

	if (db_error($result)) {
		logger_error("Unable to check project name '$project_name'" . db_error_message($result));
		return -1;
	}

	if (!db_numrows($result)) {
		/* priject name not in use. */
		return 0;
	}

	/* project name already being used. */
	return 1;
}

/*! \brief return the project backlog list
 *  \param $project ID 
 *  \param $sprinted = 1 return only project backlogs aren't in sprint
 *  \param $sprinted = 0 return all project backlogs
 *  \retval < 0 Error
 *  \retval Array
 */
function project_backlog_list($project,$tasked=0) {
	global $project_backlog_table;
	global $user_table;
	global $backlog_task;

	if ($tasked == 0) {
		$SQL = "SELECT a.id_pbacklog, a.summary, a.description, a.priority, b.name, b.lastname 
			FROM $project_backlog_table a 
			LEFT JOIN $user_table b ON b.id_user = a.id_user 
			WHERE 
			id_project=$project ORDER BY priority DESC";
	} else {
		$SQL = "SELECT a.id_pbacklog, a.summary, a.description, a.priority, b.name, b.lastname
			FROM $project_backlog_table a
			LEFT JOIN $user_table b ON b.id_user = a.id_user
			WHERE 
			id_project=$project 
			AND id_pbacklog NOT IN (SELECT id_pbacklog FROM $backlog_task)
			ORDER BY priority DESC";
	}
	$res = db_query($SQL);
	
	if (db_error($res)) {
		logger_error("Unable to get project backlog list " . db_error_message($res));
		return -1;
	}

	if (db_numrows($res) > 0) {
		$backlog_count = 0;
		while ($row = db_fetchrow($res)) {
			foreach ($row AS $key => $value) {
				$backlog[$backlog_count][$key] = $value;
			}
			$backlog_count++;
		}
		db_freeresult($res);
		return $backlog;
	} else {
		return 0;
	}
}

/*! \brief return project backlog list for an user
 *  \param $project project id
 *  \param $user user logued id
 *  \retval < 0 Error
 *  \retval project backlog list
 */ 
function project_backlog_user_list($project,$user)
{
	global $project_backlog_table;
	global $user_table;
	global $backlog_task;

	$SQL = "SELECT a.id_pbacklog, a.summary, a.description, a.priority, b.name, b.lastname 
		FROM $project_backlog_table a 
		LEFT JOIN $user_table b ON b.id_user = a.id_user 
		WHERE 
		id_project=$project 
		AND b.id_user = $user 
		AND id_pbacklog NOT IN (SELECT id_pbacklog FROM $backlog_task) 
		ORDER BY priority DESC";
	$res = db_query($SQL);

	if (db_error($res)) {
		logger_error("Unable to get project backlog list" . db_error_message($res));
		return -1;
	}

	if (db_numrows($res) > 0) {
		$backlog_count = 0;
		while ($row = db_fetchrow($res)) {
			foreach ($row AS $key => $value) {
				$backlog[$backlog_count][$key] = $value;
			}
			$backlog_count++;
		}
		db_freeresult($res);

		return $backlog;
	} else {
		return 0;
	}
}

/*! \brief return project backlog detail
 *  \param $id project backlog id
 *  \retval < 0 Error
 *  \retval Array
 */
function project_backlog_detail($id)
{
	global $project_backlog_table;

	$SQL = "SELECT * FROM $project_backlog_table WHERE id_pbacklog=$id";
	$res = db_query($SQL);

	if (db_error($res)) {
		logger_error("Unable to get project backlog detail " . db_error_message($res));
		return -1;
	}

	if (db_numrows($res) > 0) {
		$backlog_count = 0;
		while ($row = db_fetchrow($res)) {
			foreach ($row AS $key => $value) {
				$backlog[$backlog_count][$key] = $value;				
			}
			$backlog_count++;
		}
		db_freeresult($res);
		return $backlog;
	} else {
		return 0;
	}
}

/*! \brief put a project backlog to history
 *  \param $pbacklog project backlog ID
 *  \param $unit Unit name
 *  \param $summary string
 *  \param $description string
 *  \param $priority int
 *  \param $estimated time in float
 *  \param $user name 
 *  \retval < 0 Error
 *  \retval 1 Ok
 */
function project_backlog_to_history($pbacklog, $unit, $summary, $description, $priority, $estimated, $user)
{
	global $project_backlog_history_table;

	$SQL = "INSERT INTO $project_backlog_history_table
		(id_pbacklog,unit,summary,description,priority,estimated,modified,user) 
		VALUES
		($pbacklog,'$unit','$summary','$description',$priority, $estimated,'".date("y-m-d h:i:s")."','$user');";
	$res = db_query($SQL);

	if (db_error($res)) {
		logger_error(" Failed to save backlog on history " . db_error_message($res));
		return -1;
	}

	return 1;
}

/*! \brief return the project detail
 *  \param $project_id ID project
 *  \retval Array project data
 */
function project_detail($project_id) 
{
	global $project_table;

	$SQL = "SELECT * FROM $project_table WHERE id_project=$project_id";
	$res = db_query($SQL);

	if (db_error($res)) {
		logger_error(" Failed to get the project detail " . db_error_message($res));
		return -1;
	}

	if (db_numrows($res) > 0) {
		$row = db_fetchrow($res);
		return $row;
	}
}

/*! \brief modify a project
 *  \param $id
 *  \param $name
 *  \param $desc
 *  \param $date
 *  \retval < 0 on Error
 *  \retval 1 ok
 */
function project_update($id, $name, $desc, $date)
{
	global $project_table;

	$SQL = "UPDATE $project_table SET name='$name', description='$desc', 
		date_beginning='$date' WHERE id_project=$id";
	$res = db_query($SQL);

	if (db_error($res)) {
		logger_error(" Failed to update project" . db_error_message($res));
		return -1;
	}

	return 1;
}

/*! \brief add a resource to a project
 *  \param $id_project
 *  \param $id_user
 *  \param $id_role
 */
function project_add_resource($id_project, $id_user, $id_role)
{
	global $project_user_role_table;	

	$SQL = "INSERT INTO $project_user_role_table VALUES($id_project, $id_user, $id_role)";
	$res = db_query($SQL);

	if (db_error($res)) {
		logger_error(" Failed to add user to project " . db_error_message($res));
		return -1;
	}

	return 1;

}

/*
 *
 *
 */
function project_check_resource_exists($id_user, $id_project)
{
	global $project_user_role_table;

	$SQL = "SELECT * FROM $project_user_role_table WHERE id_user=$id_user AND id_project=$id_project";
	$res = db_query($SQL);
	
	logger_error($SQL);


	if (db_error($res)) {
		logger_error(" Error while check if resource exists " . db_error_message($res));
		return -1;
	}

	logger_error($SQL);

	if (db_numrows($res) > 0) {
		return 1;
	} else {
		return 0;
	}
}

?>
